IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[GetCOIDocuments]') AND objectproperty(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [dbo].[GetCOIDocuments]
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO  
  
/***************************************************************************  
* Name:    GetCOIDocuments  
* Purpose:   Returns all the COI Reports for the specified ids   
*  
*  
* PARAMETERS  
* Name    Description       
* -------------  -----------------------------------------------------------  
* @xmlListOfAdmPersonIds COI Ids  
***************************************************************************/  
  
CREATE PROCEDURE dbo.[GetCOIDocuments]  
(  
 @xmlListOfAdmPersonIds TEXT,
 @filterByLatestReportingPeriod bit  
)  
AS  
BEGIN  
 SET NOCOUNT ON  
  
 declare @admPersonIds table (Sequence int identity, [ID] int)   
   
 declare @xmlDoc int          
 exec sp_xml_preparedocument @xmlDoc output, @xmlListOfAdmPersonIds  
 insert into @admPersonIds select [Id] from openxml(@xmlDoc, '//IdList', 2) with (Id int)          
 exec sp_xml_removedocument @xmlDoc  
  
 DECLARE @latestReportingPeriod INT
 SELECT @latestReportingPeriod = MAX(Id) FROM dbo.COIReportingPeriods
   
 SELECT ADMP.LastName + ', ' + ADMP.FirstName as FullName, D.blob  
 FROM @admPersonIds Ids   
  INNER JOIN AdmPerson ADMP ON Ids.ID = ADMP.Id  
  INNER JOIN ConflictOfInterest COI ON ADMP.Id = COI.AdmPersonId  
  INNER JOIN Document D ON COI.DocumentId = D.Id   
 WHERE COI.Id in (SELECT Max(COI.Id) FROM ConflictOfInterest COI  
				  INNER JOIN @admPersonIds AP ON COI.AdmPersonId = AP.Id
                  WHERE COI.Status IN ('COISTATUS_SUBMITTED', 'COISTATUS_REVIEWED') 
                  AND (@filterByLatestReportingPeriod = 0 OR COI.ReportingPeriodId = @latestReportingPeriod)
				  GROUP BY AP.Id)  
 ORDER BY ADMP.LastName, ADMP.FirstName  
  
 SET NOCOUNT OFF  
END  
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
